from flask import jsonify, render_template, request

#
from blues.cloud import bp
from database import session_maker

# 按日统计-OSS存储使用率
oss_sql = """
SELECT
	DATE_FORMAT(record_time, '%Y-%m-%d') AS 'date',
	ROUND(
		100 * SUM(size_in_bytes) / (SUM(capacity) * 1000000000),
		2
	) AS 'total_quota_usage'
FROM
	c_oss_info
WHERE
	DATE_FORMAT(record_time, '%Y-%m-%d') BETWEEN '{}'
AND '{}'
GROUP BY
	DATE_FORMAT(record_time, '%Y-%m-%d')
ORDER BY
	DATE_FORMAT(record_time, '%Y-%m-%d') ASC;
"""

# 按日统计RDS使用率情况
rds_sql = """
SELECT
	DATE_FORMAT(a.date, '%Y-%m-%d') AS 'date',
	ROUND(
		SUM(a.cpu_avg * c.cpu) / SUM(c.cpu),
		2
	) AS 'cpu_percent',
	ROUND(
		SUM(a.mem_avg * c.mem) / SUM(c.mem),
		2
	) AS 'mem_percent',
	ROUND(AVG(disk_max), 2) AS 'disk_percent'
FROM
	cms_rds a,
	c_rds_info b,
	(
		SELECT
			db_instance_class,
			cpu,
			mem
		FROM
			class_rds
		GROUP BY
			db_instance_class
	) c
WHERE
	a.instance_id = b.db_instance_id
AND a.date = DATE_FORMAT(b.record_time, '%Y-%m-%d')
AND a.date BETWEEN '{}'
AND '{}' #统计该区间数据
AND b.db_instance_class = c.db_instance_class
GROUP BY
	a.date
ORDER BY
	a.date ASC;
"""

# 按日统计ECS使用率情况
ecs_sql = """
SELECT
	DATE_FORMAT(a.date, '%Y-%m-%d') AS 'date',
	ROUND(
		SUM(b.cpu * a.cpu_avg) / SUM(b.cpu),
		2
	) AS 'cpu_percent',
	ROUND(
		SUM(b.memory * a.mem_avg) / SUM(b.memory),
		2
	) AS 'mem_percent',
	ROUND(
		SUM(c.disk_size * c.disk_usage) / SUM(c.disk_size),
		2
	) AS 'disk_percent'
FROM
	cms_ecs a,
	c_ecs_info b,
	cms_ecs_disk c
WHERE
	a.instance_id = b.instance_id
AND a.date = c.date
AND a.date = DATE_FORMAT(b.record_time, '%Y-%m-%d')
AND a.date BETWEEN '{}'
AND '{}'
GROUP BY
	a.date
ORDER BY
	a.date ASC;
"""

# 按日统计EIP带宽使用率
eip_sql = """
SELECT
	date,
	ROUND(
		100 * SUM(net_rx_max) / SUM(bandwidth * 1000000),
		2
	) AS '带宽最大使用率%',
	ROUND(
		100 * SUM(net_rx_min) / SUM(bandwidth * 1000000),
		2
	) AS '带宽最小使用率%',
	ROUND(
		100 * SUM(net_rx_avg) / SUM(bandwidth * 1000000),
		2
	) AS '带宽平均使用率%'
FROM
	cms_eip
WHERE
	date BETWEEN '{}'
AND '{}' #取当月全部数据来统计
GROUP BY
	date;
"""

odps_sql = """
SELECT
	ds,
	SUM(storage_size),
	ROUND(
		IFNULL(SUM(total_cost_cpu), 0) / 100 / 3600,
		0
	) AS '当日消耗CPU核数合计(核*小时)',
	ROUND(
		IFNULL(SUM(total_cost_mem), 0) / 1024 / 3600,
		0
	) AS '当日消耗内存合计(GB*小时)',
	ROUND(
		IFNULL(SUM(storage_size), 0) / 3 / 1024,
		0
	) AS '当日存储使用量(TB)',
	SUM(total_task_num) AS '当日任务数'
FROM
	abm_saas_ag.odps_project_info_topn
WHERE
	ds BETWEEN '{}'
AND '{}'
GROUP BY
	ds;
"""


def ecs_daily_stats(start, end) -> list:
    data_li = []
    with session_maker() as session:
        rs = session.execute(ecs_sql.format(start, end))  # res是获取的对象
        for row in rs:
            tmp_dict = {
                'date': row[0], 'cpu_percent': row[1], 'mem_percent': row[2], 'disk_percent': row[3]
            }
            data_li.append(tmp_dict)
    return data_li


def rds_daily_stats(start, end) -> list:
    data_li = []
    with session_maker() as session:
        rs = session.execute(rds_sql.format(start, end))  # res是获取的对象
        for row in rs:
            tmp_dict = {
                'date': row[0], 'cpu_percent': row[1], 'mem_percent': row[2], 'disk_percent': row[3]
            }
            data_li.append(tmp_dict)
    return data_li


def oss_daily_stats(start, end) -> list:
    data_li = []
    with session_maker() as session:
        rs = session.execute(oss_sql.format(start, end))  # res是获取的对象
        for row in rs:
            tmp_dict = {
                'date': row[0], 'total_quota_usage': row[1]
            }
            data_li.append(tmp_dict)
    return data_li


def eip_daily_stats(start, end) -> list:
    data_li = []
    with session_maker() as session:
        rs = session.execute(eip_sql.format(start, end))  # res是获取的对象
        for row in rs:
            tmp_dict = {
                'date': str(row[0]), 'bandwidth_max_usage': row[1], 'bandwidth_min_usage': row[2],
                'bandwidth_avg_usage': row[3]
            }
            data_li.append(tmp_dict)
    return data_li


def odps_daily_stats(start, end) -> list:
    data_li = []
    with session_maker() as session:
        rs = session.execute(odps_sql.format(start, end))  # res是获取的对象
        for row in rs:
            tmp_dict = {
                'date': row[0], 'cpu_core_hours': row[2], 'mem_gb_hours': row[3], 'storage_size': row[4],
                'tasks': row[5]
            }
            data_li.append(tmp_dict)
    return data_li


@bp.route('/api/v1/cloud/ecs_daily_stats', methods=['GET'])
def ecs_daily_stats_api():
    start = request.args.get('start')
    end = request.args.get('end')
    print(start, end)
    data_li = ecs_daily_stats(start, end)
    return jsonify({"code": 200, "data": data_li})


@bp.route('/api/v1/cloud/rds_daily_stats', methods=['GET'])
def rds_daily_stats_api():
    start = request.args.get('start')
    end = request.args.get('end')
    print(start, end)
    data_li = rds_daily_stats(start, end)
    return jsonify({"code": 200, "data": data_li})


@bp.route('/api/v1/cloud/oss_daily_stats', methods=['GET'])
def oss_daily_stats_api():
    start = request.args.get('start')  # 2023-09-01
    end = request.args.get('end')  # 2023-09-20
    print(start, end)
    data_li = oss_daily_stats(start, end)
    return jsonify({"code": 200, "data": data_li})


@bp.route('/api/v1/cloud/eip_daily_stats', methods=['GET'])
def eip_daily_stats_api():
    start = request.args.get('start')  # 2023-09-01
    end = request.args.get('end')  # 2023-09-20
    print(start, end)
    data_li = eip_daily_stats(start, end)
    return jsonify({"code": 200, "data": data_li})


@bp.route('/api/v1/cloud/odps_daily_stats', methods=['GET'])
def odps_daily_stats_api():
    start = request.args.get('start')  # 20230901
    end = request.args.get('end')  # 20230930
    # odps的日期传参需要删除多余的符号"-"
    start = start.replace("-", "")
    end = end.replace("-", "")
    print(start, end)
    data_li = odps_daily_stats(start, end)
    return jsonify({"code": 200, "data": data_li})


# 渲染web页面
@bp.route('/cloud/daily_stats')
def resource_daily_stats_page():
    return render_template('resource_daily_stats.html', active='cloud_daily')


if __name__ == '__main__':
    a = ecs_daily_stats(ecs_sql)
    print(a)
